package com.my.xl.salary.billdemo.service;

import com.alibaba.fastjson.JSONObject;
import com.my.xl.salary.billdemo.dao.BillDao;
import com.my.xl.salary.billdemo.pojo.SalaryBill;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

@Service  //导入导出excel
public class ExportService {
    @Autowired
    private AESToolService aesService;
    @Autowired
    private DingDingService dd;
    @Autowired
    private BillDao billDao;

    //导入的excel文件路径 解析数据
    public boolean importExecl(Workbook wb0,String access_token) {
        System.out.println("解析excel表格数据,保存开始!!!!"+new Date());
        if (wb0==null){
            System.out.println("excel文件为空!!!");
            return false;
        }
        SimpleDateFormat d2 = new SimpleDateFormat("YYYY-MM-dd");
        SimpleDateFormat y = new SimpleDateFormat("YYYY");
        SimpleDateFormat m = new SimpleDateFormat("MM");
        Sheet sheet=wb0.getSheetAt(0); //获取第一张Sheet表
        //int rowNum=sheet.getLastRowNum()+1;  //获取总行数
        //名字列表
        ArrayList<Object> nameArr = new ArrayList<>();
        //{名字:userId}
        HashMap<String, String> map = new HashMap<>();
        for (Row r:sheet) {
            if(r.getRowNum()==0){
                continue;
            }
            String name= r.getCell(0).getStringCellValue();//姓名
            if(!nameArr.contains(name)){
                JSONObject result = dd.searchEmp(name, access_token);
                String userid = result.getString("userid");
                nameArr.add(name);
                map.put(name,userid);
            }
            String basic4 = r.getCell(1).getStringCellValue();//基本工资
            String post4 =r.getCell(2).getStringCellValue() ;//岗位工资
            String comprohen4 = r.getCell(3).getStringCellValue();//综合贴
            String reward4 = r.getCell(4).getStringCellValue();//奖金
            String social4 = r.getCell(5).getStringCellValue();//社保
            String public_4 =r.getCell(6).getStringCellValue();//公积金
            String leave4 =r.getCell(7).getStringCellValue();//病事假
            String pre_tax4 =r.getCell(8).getStringCellValue();//税前抵扣
            String per_tax4 =r.getCell(9).getStringCellValue();//个税
            Date date = r.getCell(10).getDateCellValue();//日期
            //String comment = r.getCell(11).getStringCellValue();//备注
            double basic=0;
            double post =0;
            double comprohen =0;
            double reward=0;
            double social =0;
            double public_ =0;
            double leave=0;
            double pre_tax =0;
            double per_tax =0;
            if (basic4!=null){
                basic =Double.parseDouble(basic4) ;//基本工资
            }
            if (post4!=null){
                post =Double.parseDouble(post4) ;//岗位工资
            }
            if (comprohen4!=null){
                comprohen = Double.parseDouble(comprohen4);//综合贴
            }
            if (reward4!=null){
                reward = Double.parseDouble(reward4);//奖金
            }
            if (social4!=null){
                social = Double.parseDouble(social4);//社保
            }
            if (public_4!=null){
                public_ = Double.parseDouble(public_4);//公积金
            }
            if (leave4!=null){
                leave = Double.parseDouble(leave4);//病事假
            }
            if (pre_tax4!=null){
                pre_tax = Double.parseDouble(pre_tax4);//税前抵扣
            }
            if (per_tax4!=null){
                per_tax = Double.parseDouble(per_tax4);//个税
            }
            double gross_=0;  //应发工资
            double actual = 0;//实发工资
            gross_=basic+post+comprohen+reward;
            actual=gross_-(social+public_+leave+pre_tax+per_tax);
            String basic2 = String.format("%.2f", basic);
            String post2 = String.format("%.2f", post);
            String comprohen2 = String.format("%.2f", comprohen);
            String reward2 = String.format("%.2f", reward);
            String social2 = String.format("%.2f", social);
            String public_2 = String.format("%.2f", public_);
            String leave2 = String.format("%.2f", leave);
            String pre_tax2 = String.format("%.2f", pre_tax);
            String per_tax2 = String.format("%.2f", per_tax);
            String gross_2 = String.format("%.2f", gross_);
            String actual2 = String.format("%.2f", actual);
            //加密数据
            String basic3 = aesService.base64Encode(basic2);
            String post3 = aesService.base64Encode(post2);
            String comprohen3 = aesService.base64Encode(comprohen2);
            String reward3 = aesService.base64Encode(reward2);
            String social3 = aesService.base64Encode(social2);
            String public_3 = aesService.base64Encode(public_2);
            String leave3 = aesService.base64Encode(leave2);
            String pre_tax3 = aesService.base64Encode(pre_tax2);
            String per_tax3 = aesService.base64Encode(per_tax2);
            String gross_3 = aesService.base64Encode(gross_2);
            String actual3 = aesService.base64Encode(actual2);
            String userid=null;
            if (nameArr.contains(name)){
                if (userid==null){
                    userid=map.get(name);
                }
            }
            SalaryBill s = new SalaryBill();
            s.setUserid(userid);
            s.setUsername(name);
            s.setDate2(d2.format(date));
            s.setDate(date);
            s.setMonth(m.format(date));
            s.setYear(y.format(date));
            s.setComment(1);
            s.setBasicsalary(basic3);
            s.setPostsalary(post3);
            s.setComprohensivesubsidy(comprohen3);
            s.setReward(reward3);
            s.setGrosssalary(gross_3);
            s.setSocialsecurity(social3);
            s.setPubreservefunds(public_3);
            s.setLeavesalary(leave3);
            s.setPretaxdeduction(pre_tax3);
            s.setPersonaltax(per_tax3);
            s.setActualsalary(actual3);
            //根据name month year 查询之前是否导入过工资
            SalaryBill bill = billDao.isExportByNameAndMOnth(name, m.format(date), y.format(date));
            if (bill!=null){
                System.out.println("之前导入过工资,修改工资条!!!!"+name);
                s.setId(bill.getId());
                boolean b2=billDao.updateBill(s);
                if(b2){
                    System.out.println("修改成功!!!!"+name+">>>>"+userid);
                }else{
                    System.out.println("修改失败!!!!"+name+">>>>"+userid);
                }
            }else{
                System.out.println("插入工资条数据!!!!"+name);
                int num= billDao.insertBill(s);
                if(num>0){
                    System.out.println("插入一条数据成功!!!!"+name+">>>>"+userid);
                }else{
                    System.out.println("插入一条数据失败!!!!"+name+">>>>"+userid);
                }
            }
        }
        nameArr=null;
        map=null;
        y=null;
        m=null;
        d2=null;
        System.out.println("解析excel表格数据,保存结束!!!!!"+new Date());
        return true;
    }






}
